<?php
//error_reporting(E_ALL ^ E_NOTICE);
include ("../inc/db_connect.php");
include ("../inc/functions.php");
include ('excel_reader2.php');
$data = new Spreadsheet_Excel_Reader("udaje.xls",false,'utf-8');
?>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <style>
            table.excel {
                border-style:ridge;
                border-width:1;
                border-collapse:collapse;
                font-family:sans-serif;
                font-size:12px;
            }
            table.excel thead th, table.excel tbody th {
                background:#CCCCCC;
                border-style:ridge;
                border-width:1;
                text-align: center;
                vertical-align:bottom;
            }
            table.excel tbody th {
                text-align:center;
                width:20px;
            }
            table.excel tbody td {
                vertical-align:bottom;
            }
            table.excel tbody td {
                padding: 0 3px;
                border: 1px solid #EEEEEE;
            }
        </style>
    </head>

    <body>
        <?php

        $r = $data->rowcount($sheet_index=0);
        $c = $data->colcount($sheet_index=0);

        for ($i=2;$i<$r+1;$i++) {
            for ($j=1;$j<$c+1;$j++) {
                echo '<b style="color: red">'.$j."</b>:";
                echo $data->val($i,$j);
                echo '|';
            }
            //if (($data->val($i,1)==641) || ($data->val($i,1)==725)) {
            /// meno, priezvisko, titul
            $s1 = explode(',', $data->val($i,4));
            echo '<br/>';
            if (isset($s1[1])) {
                echo $titul = trim($s1[1]);
            } else {
                echo $titul = -1;
            }
            echo '<br/>';
            $pos = strpos($s1[0],'  ');
            if ($pos===false) {

            } else {
                $s1[0] = substr_replace($s1[0], ' ', $pos, 2);
            }
            $mp = explode(' ',$s1[0]);
            echo $meno = iconv('windows-1250', 'utf-8', $mp[1]);
            echo '<br/>';
            echo $priezvisko = iconv('windows-1250', 'utf-8', $mp[0]);
            echo '<br/>';

            echo $rodnePriezvisko = iconv('windows-1250', 'utf-8', trim($data->val($i,5)));
            echo '<br/>';

            // datum narodenia den/mesiac/rok 0/1/2
            $s1 = explode(' ', $data->val($i,6));
            $dn = explode('/', $s1[0]);
            var_dump($dn);
            // adresa
            echo $ulica = iconv('windows-1250', 'utf-8', $data->val($i,7));
            echo '<br/>';
            echo $psc = $data->val($i,8);
            echo '<br/>';
            echo $mesto = iconv('windows-1250', 'utf-8', $data->val($i,9));
            echo '<br/>';
            //rodne cislo
            echo $cislo1 = substr($data->val($i,11),0,6);
            echo '<br/>';
            echo $cislo2 = substr($data->val($i,11),6);
            echo '<br/>';
            //druh pracovneho pomeru
            echo $druh = $data->val($i,12);
            //datum prichodu den/mesiac/rok 0/1/2
            $s1 = explode(' ', $data->val($i,13));
            $dp = explode('/', $s1[0]);
            var_dump($dp);
            //datum odchodu den/mesiac/rok 0/1/2
            if (trim($data->val($i,14))!='') {
                $s1 = explode(' ', $data->val($i,14));
                $do = explode('/', $s1[0]);
            } else {
                $do = array('00','00','0000');
            }
            var_dump($do);

            $user_s = "UPDATE z SET
                        datum_prichodu='".$dp[2]."-".$dp[1]."-".$dp[0]."',
                        datum_odchodu='".$do[2]."-".$do[1]."-".$do[0]."',
                        meno='".$meno."',
                        priezvisko='".$priezvisko."'
                        WHERE id='".$data->val($i,1)."'";
            $user_q = MySQL_Query ($user_s);
            //trvale bydlisko
            $x_s = "SELECT * FROM z_bydlisko_trvale WHERE zid='".$data->val($i,1)."' ";
            $x_q = MySQL_Query ($x_s);
            $x = MySQL_Fetch_Array($x_q);
            if (isset($x['id'])) {
                echo $y_s = "UPDATE z_bydlisko_trvale SET ulica='".$ulica."', psc='".$psc."', mesto='".$mesto."', datum_pridania='".date('Y-n-d')."' WHERE id='".$x['id']."'";
                $y_q = MySQL_Query ($y_s);
                echo '<p style="color:red">Bydlisko'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
            } else {
                echo $y_s = "INSERT INTO z_bydlisko_trvale (id,ulica,psc,mesto,tel,datum_pridania,zid) VALUES (NULL,'".$ulica."','".$psc."','".$mesto."','','".date('Y-n-d')."',".$data->val($i,1).")";
                echo '<p style="color:red">Bydlisko'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                $y_q = MySQL_Query ($y_s);
            }
            //datum narodenia
            $x_s = "SELECT * FROM z_datum_narodenia WHERE zid='".$data->val($i,1)."' ";
            $x_q = MySQL_Query ($x_s);
            $x = MySQL_Fetch_Array($x_q);
            if (isset($x['id'])) {
                echo $y_s = "UPDATE z_datum_narodenia SET datum_narodenia='".$dn[2]."-".$dn[1]."-".$dn[0]."', datum_pridania='".date('Y-n-d')."' WHERE id='".$x['id']."'";
                $y_q = MySQL_Query ($y_s);
                echo '<p style="color:red">DAtum narodenia'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
            } else {
                echo $y_s = "INSERT INTO z_datum_narodenia (id,datum_narodenia,datum_pridania,zid) VALUES (NULL,'".$dn[2]."-".$dn[1]."-".$dn[0]."','".date('Y-n-d')."',".$data->val($i,1).")";
                echo '<p style="color:red">DAtum narodenia'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                $y_q = MySQL_Query ($y_s);
            }
            // druh pomeru
            $x_s = "SELECT * FROM z_druh_pomeru WHERE zid='".$data->val($i,1)."' ";
            $x_q = MySQL_Query ($x_s);
            $x = MySQL_Fetch_Array($x_q);
            if (isset($x['id'])) {
                echo $y_s = "UPDATE z_druh_pomeru SET druh='".$druh."', datum_pridania='".date('Y-n-d')."' WHERE id='".$x['id']."'";
                $y_q = MySQL_Query ($y_s);
                echo '<p style="color:red">Druh pomeru'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
            } else {
                echo $y_s = "INSERT INTO z_druh_pomeru (id,druh,datum_pridania,zid) VALUES (NULL,'".$druh."','".date('Y-n-d')."',".$data->val($i,1).")";
                echo '<p style="color:red">Druh pomeru'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                $y_q = MySQL_Query ($y_s);
            }
            // meno
            $x_s = "SELECT * FROM z_meno WHERE zid='".$data->val($i,1)."' ";
            $x_q = MySQL_Query ($x_s);
            $x = MySQL_Fetch_Array($x_q);
            if (isset($x['id'])) {
                echo $y_s = "UPDATE z_meno SET meno='".$meno."', datum_pridania='".date('Y-n-d')."' WHERE id='".$x['id']."'";
                $y_q = MySQL_Query ($y_s);
                echo '<p style="color:red">Meno'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
            } else {
                echo $y_s = "INSERT INTO z_meno (id,meno,datum_pridania,zid) VALUES (NULL,'".$meno."','".date('Y-n-d')."',".$data->val($i,1).")";
                echo '<p style="color:red">Meno'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                $y_q = MySQL_Query ($y_s);
            }
            // priezvisko
            $x_s = "SELECT * FROM z_priezvisko WHERE zid='".$data->val($i,1)."' ";
            $x_q = MySQL_Query ($x_s);
            $x = MySQL_Fetch_Array($x_q);
            if (isset($x['id'])) {
                echo $y_s = "UPDATE z_priezvisko SET priezvisko='".$priezvisko."', datum_pridania='".date('Y-n-d')."' WHERE id='".$x['id']."'";
                $y_q = MySQL_Query ($y_s);
                echo '<p style="color:red">priezvisko'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
            } else {
                echo $y_s = "INSERT INTO z_priezvisko (id,priezvisko,datum_pridania,zid) VALUES (NULL,'".$priezvisko."','".date('Y-n-d')."',".$data->val($i,1).")";
                echo '<p style="color:red">priezvisko'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                $y_q = MySQL_Query ($y_s);
            }
            // rodne priezvisko
            if (trim($rodnePriezvisko)!='') {
                $x_s = "SELECT * FROM z_priezvisko_rodne WHERE zid='".$data->val($i,1)."' ";
                $x_q = MySQL_Query ($x_s);
                $x = MySQL_Fetch_Array($x_q);
                if (isset($x['id'])) {
                    echo $y_s = "UPDATE z_priezvisko_rodne SET priezvisko_rodne='".$rodnePriezvisko."', datum_pridania='".date('Y-n-d')."' WHERE id='".$x['id']."'";
                    $y_q = MySQL_Query ($y_s);
                    echo '<p style="color:red">priezvisko_rodne'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                } else {
                    echo $y_s = "INSERT INTO z_priezvisko_rodne (id,priezvisko_rodne,datum_pridania,zid) VALUES (NULL,'".$rodnePriezvisko."','".date('Y-n-d')."',".$data->val($i,1).")";
                    echo '<p style="color:red">priezvisko_rodne'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                    $y_q = MySQL_Query ($y_s);
                }
            }
            // rodne cislo
            $x_s = "SELECT * FROM z_rodne_cislo WHERE zid='".$data->val($i,1)."' ";
            $x_q = MySQL_Query ($x_s);
            $x = MySQL_Fetch_Array($x_q);
            if (isset($x['id'])) {
                echo $y_s = "UPDATE z_rodne_cislo SET cislo1='".$cislo1."', cislo2='".$cislo2."', datum_pridania='".date('Y-n-d')."' WHERE id='".$x['id']."'";
                $y_q = MySQL_Query ($y_s);
                echo '<p style="color:red">rodne_cislo'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
            } else {
                echo $y_s = "INSERT INTO z_rodne_cislo (id,cislo1,cislo2,datum_pridania,zid) VALUES (NULL,'".$cislo1."','".$cislo2."','".date('Y-n-d')."',".$data->val($i,1).")";
                echo '<p style="color:red">rodne_cislo'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                $y_q = MySQL_Query ($y_s);
            }
            //titul
            if ($titul!=-1) {
                $x_s = "SELECT * FROM z_titul WHERE zid='".$data->val($i,1)."' ";
                $x_q = MySQL_Query ($x_s);
                $x = MySQL_Fetch_Array($x_q);
                if (isset($x['id'])) {
                    echo $y_s = "UPDATE z_titul SET titul='".$titul."', datum_pridania='".date('Y-n-d')."' WHERE id='".$x['id']."'";
                    $y_q = MySQL_Query ($y_s);
                    echo '<p style="color:red">titul'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                } else {
                    echo $y_s = "INSERT INTO z_titul (id,titul,datum_pridania,zid) VALUES (NULL,'".$titul."','".date('Y-n-d')."',".$data->val($i,1).")";
                    echo '<p style="color:red">titul'."MySQL error ".mysql_errno().": ".mysql_error()."\n<br>".$z_uloz_i.'</p>';
                    $y_q = MySQL_Query ($y_s);
                }
            }
            //}
            echo '<br/>';
        }

        echo $data->dump(true,true);
        ?>
        <?php
//echo $data->val(10,A);
        ?>
    </body>
</html>
